package com.lexton.toolbox.parser;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Workbook;

import com.lexton.util.StringUtil;
import com.lexton.util.excel.ExcelReadUtils;

/**
 * 将厂家提供的各式各样的表格尝试转换为公司导入的标准表格
 * @author Jimmy
 */
public class SupplierExcelParser {
	public static Map<String, Integer> getHeaderConnect(Map<Integer, Object> row) {
		Map<String, Integer> headerConnect = new HashMap<String, Integer>();
			
		Iterator<Integer> iter = row.keySet().iterator();
		while (iter.hasNext()) {
			Integer i = iter.next();
			Object o = row.get(i);
			if ("产品名称".equals(o.toString())) {
				headerConnect.put("name", i);
			} else if ("产品编码".equals(o.toString())) {
				headerConnect.put("sn", i);
			} else if ("产品单位".equals(o.toString())) {
				headerConnect.put("unit", i);
			} else if ("产品规格".equals(o.toString().split("\\|")[0])) {//产品规格|mm->cm或者m->cm或者
				headerConnect.put("spec", i);
				String[] express = o.toString().split("\\|");
				if (express.length >= 2 && express[1].indexOf(">")>0) {
					if ("mm".equals(express[1].split(">")[0])) {
						headerConnect.put("sourceSpec", 1);
					} else if ("cm".equals(express[1].split(">")[0])) {
						headerConnect.put("sourceSpec", 2);
					} else if ("m".equals(express[1].split(">")[0])) {
						headerConnect.put("sourceSpec", 3);
					}
					
					if ("mm".equals(express[1].split(">")[1])) {
						headerConnect.put("targetSpec", 1);
					} else if ("cm".equals(express[1].split(">")[1])) {
						headerConnect.put("targetSpec", 2);
					} else if ("m".equals(express[1].split(">")[1])) {
						headerConnect.put("targetSpec", 3);
					}
				}
				if (express.length == 3) {//H*D与D*H
					String[] unitFormat = express[2].split("\\*");
					for (int j=0; j<unitFormat.length; j++) {
						if ("LWHD".indexOf(unitFormat[j]) > 0) {
							headerConnect.put("specFormat"+j, "LWHD".indexOf(unitFormat[j]));
						}
					}
				}
			} else if ("产品材质".equals(o.toString())) {
				headerConnect.put("material", i);
			} else if ("产品成本价".equals(o.toString())) {
				headerConnect.put("price", i);
			} else if ("是否是推荐产品".equals(o.toString())) {
				headerConnect.put("recommend", i);
			} else if ("数量".equals(o.toString())) {
				headerConnect.put("quantity", i);
			} else if ("家装情景描述".equals(o.toString())) {
				headerConnect.put("attr1", i);
			} else if ("商品实拍描述".equals(o.toString())) {
				headerConnect.put("attr2", i);
			} else if ("商品细节描述".equals(o.toString())) {
				headerConnect.put("detail", i);
			} else if ("选型".equals(o.toString())) {
				headerConnect.put("attr3", i);
			} else if ("颜色".equals(o.toString())) {
				headerConnect.put("color", i);
			} else if ("积分".equals(o.toString())) {
				headerConnect.put("credit", i);
			} else if ("显示面价".equals(o.toString())) {
				headerConnect.put("showprice", i);
			} else if ("展销状态".equals(o.toString())) {
				headerConnect.put("isexhibit", i);
			} else if ("图片".equals(o.toString())) {
				headerConnect.put("picture", i);
			}
		}

		return headerConnect;
	}
	
	/**
	 * 对一个工作表的数据进行转换
	 * @param titleMode 
	 */
	public static Map<Integer, Map<Integer,Object>> convert(Map<String, Integer> headerConnect, Map<Integer, Map<Integer,Object>> map) {
		Object lastSN = null;
		String sourceSpec = "2";
		String targetSpec = "2";
		if (headerConnect.containsKey("sourceSpec") && headerConnect.containsKey("targetSpec")) {
			sourceSpec = headerConnect.get("sourceSpec").toString();
			targetSpec = headerConnect.get("targetSpec").toString();
		}

		List<String> specFormat = new ArrayList<String>();
		for (int i=0; i<"LWHD".length(); i++) {
			if (headerConnect.get("specFormat"+i) != null)
				specFormat.add(String.valueOf("LWHD".charAt(headerConnect.get("specFormat"+i))));
		}

		Map<Integer, Map<Integer,Object>> list = new HashMap<Integer, Map<Integer,Object>>();
		
		//此处不能用for (int i=0; i<map.size(); i++)
		Iterator<Integer> iter = map.keySet().iterator();
		while(iter.hasNext()) {
			Map<Integer,Object> row = map.get(iter.next());
			Map<Integer, Object> product = new HashMap<Integer, Object>();
			Object name = (row.get(headerConnect.get("name")));
			product.put(0, name);
			
			Object sn = row.get(headerConnect.get("sn"));
			if (sn == null || "".equals(sn.toString().trim())) {
				Object spec = row.get(headerConnect.get("spec"));
				if (spec == null || "".equals(spec.toString().trim())) {
					continue;
				}
				//判断前一行是否合并了单元格
				if (lastSN != null && !"".equals(lastSN.toString().trim())) {
					product.put(1, lastSN);
				}
			}
			product.put(1, sn);
			
			Object unit = row.get(headerConnect.get("unit"));
			product.put(2, unit);
			
			Object spec = row.get(headerConnect.get("spec"));
			StringBuilder sb = new StringBuilder();
			if (spec != null) {
				spec = spec.toString().replaceAll("X", "*").replaceAll("x", "*");
				if (spec.toString().contains("*")) {
					String[] parts = spec.toString().split("\\*");
					
					//提取数字，保留字母
					for (int i=0;i<parts.length; i++) {
						String part = parts[i];
						String value = "";
						try {
							Double figure = StringUtil.pickFigure(part);
							
							//mm:1 cm:2 m:3
							if (sourceSpec.equals(targetSpec)) {
								value = String.valueOf(figure).replace(".0", "");
							} else if ("2".equals(targetSpec.toLowerCase()) 
									&& "1".equals(sourceSpec.toLowerCase())) {
								value = String.valueOf(figure / 10).replace(".0", "");
							} else if ("3".equals(targetSpec.toLowerCase()) 
									&& "1".equals(sourceSpec.toLowerCase())) {
								value = String.valueOf(figure / 1000).replace(".0", "");
							} else if ("3".equals(targetSpec.toLowerCase()) 
									&& "2".equals(sourceSpec.toLowerCase())) {
								value = String.valueOf(figure / 100).replace(".0", "");
							} else if ("2".equals(sourceSpec.toLowerCase()) 
									&& "1".equals(targetSpec.toLowerCase())) {
								value = String.valueOf(figure * 10).replace(".0", "");
							} else if ("3".equals(sourceSpec.toLowerCase()) 
									&& "1".equals(targetSpec.toLowerCase())) {
								value = String.valueOf(figure * 100).replace(".0", "");
							}  else if ("3".equals(sourceSpec.toLowerCase()) 
									&& "2".equals(targetSpec.toLowerCase())) {
								value = String.valueOf(figure * 100).replace(".0", "");
							}
						} catch (Exception e) {
							value = part;
						}
						
						if (specFormat.size()>=0 && i<specFormat.size()) {
							sb.append("*" + specFormat.get(i) + value);
						} else {
							sb.append("*" + StringUtil.pickLetter(part) + value);
						}
						
						
						
						
					}
				}
				
				if (sb.toString().length() > 0) {
					product.put(3, sb.toString().substring(1));
				} else {
					product.put(3, spec);
				}
				
				Object material = row.get(headerConnect.get("material"));
				product.put(4, material);
				
				Object price = row.get(headerConnect.get("price"));
				product.put(5, price);
				
				Object recommend = row.get(headerConnect.get("recommend"));
				if (recommend == null || "".equals(recommend.toString().trim())) recommend = "不是";
				product.put(6, recommend);
				
				Object quantity = row.get(headerConnect.get("quantity"));
				if (quantity == null || "".equals(quantity.toString().trim())) quantity = 1000;
				product.put(7, quantity);
				
				Object attr0 = row.get(headerConnect.get("attr1"));
				product.put(8, attr0);
				
				Object attr2 = row.get(headerConnect.get("attr2"));
				product.put(9, attr2);
				
				Object detail = row.get(headerConnect.get("detail"));
				product.put(10, detail);
				
				Object attr1 = row.get(headerConnect.get("attr3"));
				product.put(11, attr1);
				
				Object color = row.get(headerConnect.get("color"));
				product.put(12, color);
				
				Object credit = row.get(headerConnect.get("credit"));
				product.put(13, credit);
				
				Object showPrice = row.get(headerConnect.get("showprice"));
				if (showPrice == null || "".equals(showPrice.toString().trim())) showPrice = "YES";
				product.put(14, showPrice);
				
				Object isExhibit = row.get(headerConnect.get("isexihibit"));
				if (isExhibit == null || "".equals(isExhibit.toString().trim())) isExhibit = "YES";
				product.put(15, isExhibit);
				
				Object picture = row.get(headerConnect.get("picture"));
				if (picture == null || "".equals(picture.toString().trim())) picture = "";
				product.put(16, picture);
				//给表头留位置
				list.put(list.size()+1, product);
			}
		}
		
		return list;
	}
	
	/**
	 * 解析pageMode==STYLE01
	 */
	public static List<Map<Integer, Map<Integer,Object>>> parse(ExcelReadUtils excelReader, Map<Integer, String> pickedSheets) throws Exception {
		
		List<Map<Integer, Map<Integer,Object>>> content = excelReader.readExcelContent(pickedSheets);
		
		Map<Integer, Object> standardHeader = SupplierExcelParser.getStandardHeader();
		//表头关联
		Map<String, Integer> headerConnect = SupplierExcelParser.getHeaderConnect(content.get(0).get(0));
		
		List<Map<Integer, Map<Integer,Object>>> list = new ArrayList<Map<Integer, Map<Integer,Object>>>();
		for (int i=0; i<content.size(); i++) {
			Map<Integer, Map<Integer,Object>> map = content.get(i);
			if ("EVERY_SHEET".equals(excelReader.getHeaderMode())) {
				headerConnect = SupplierExcelParser.getHeaderConnect(map.get(0));
			}
			
			Map<Integer, Map<Integer,Object>> temp = SupplierExcelParser.convert(headerConnect, map);
			temp.put(0, standardHeader);
			
			list.add(temp);
		}
		
		return list;
	}
	
	private static Map<Integer, Object> getStandardHeader() {
		Map<Integer, Object> standardHeader = new HashMap<Integer, Object>();
		standardHeader.put(0, "产品名称");
		standardHeader.put(1, "产品编码");
		standardHeader.put(2, "产品单位");
		standardHeader.put(3, "产品规格");
		standardHeader.put(4, "产品材质");
		standardHeader.put(5, "产品成本价");
		standardHeader.put(6, "是否是推荐产品");
		standardHeader.put(7, "数量");
		standardHeader.put(8, "家装情景描述");
		standardHeader.put(9, "商品实拍描述");
		standardHeader.put(10, "商品细节描述");
		standardHeader.put(11, "选型");
		standardHeader.put(12, "颜色");
		standardHeader.put(13, "积分");
		standardHeader.put(14, "显示面价");
		standardHeader.put(15, "展销状态");
		standardHeader.put(16, "图片");
		return standardHeader;
	}
	
	public static void parseToFile(String filePath, Map<Integer, 
			String> pickedSheets, String headerMode, String layoutMode, String exportPath, boolean b) throws Exception {
		if (pickedSheets == null) {
			pickedSheets = new HashMap<Integer, String>();
		}
		
		if (headerMode == null || "".equals(headerMode)) {
			headerMode = "EVERY_SHEET";
		}
		ExcelReadUtils excelReader = new ExcelReadUtils(filePath, headerMode, layoutMode);
		List<Map<Integer, Map<Integer,Object>>> list = SupplierExcelParser.parse(excelReader, pickedSheets);
		Workbook workbook = ExcelReadUtils.writeToExcel(pickedSheets, list);
		excelReader.exportExcel(workbook, exportPath);
		if (b) {
			excelReader.exportImages(pickedSheets, exportPath);
		}
	}
	
	public static void main(String[] args) throws Exception {
		//String filepath = "C:/Users/Jimmy/Desktop/供应商/2016海景家具报价表20160819.xls";
		//String filepath = "C:/Users/Jimmy/Desktop/供应商/A产品明细 - 附图报价外销.xlsx";
		//String filePath = "C:/Users/Jimmy/Desktop/供应商/蒂美嘉饰品报价20160516.xls";
		//SupplierExcelParser.parseToFile(filePath, null, null);
		
		Map<String, String> map = new HashMap<String, String>();
		map.put("产品名称", String.valueOf(10));
		
		System.out.println(map.get("产品名称"));
	}

	
}
